package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

var db *sql.DB

type User struct {
	id      int
	balance int
}

type UserMoneyLogger struct {
	id     int
	userID int
	number int
}

func main() {
	dsn := "root:123456@tcp(127.0.0.1:3306)/test"
	var err error
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	err = db.Ping()
	if err != nil {
		panic(err)
	}
	/*
	   阿里云 默认级别 RC 如何解决幻读读问题：
	   start transaction
	   select id,balance from users where id=1 for update;
	   SELECT SLEEP(10);
	   select sum(number) from user_balance_loggers where user_id=1;
	   commit

	   start transaction
	   insert into user_balance_loggers(user_id,number) values(1, -50);
	   update users set balance=balance-50 where id=1;
	   commit
	*/
	db.Exec("SET session transaction_isolation = 'REPEATABLE-READ'")
	//db.Exec("SET session transaction_isolation = 'READ-COMMITTED'")
	var isolationLevel string
	err = db.QueryRow("SELECT @@transaction_isolation;").Scan(&isolationLevel)
	if err != nil {
		panic(err)
	}
	fmt.Printf("当前事务隔离级别: %s\n", isolationLevel)

	// 对账业务
	userID := 1
	go reconciliation(userID)
	insert(userID)

	time.Sleep(10 * time.Second)
}

func insert(userID int) {
	time.Sleep(1 * time.Second)

	conn, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed :", err)
		return
	}
	conn.Exec("update users set balance=balance-? where id=?", 50, userID)
	conn.Exec("insert into user_balance_loggers(user_id,number) values(?, ?)", userID, -50)
	conn.Commit()
	fmt.Println("插入一条日志数据")
}

func reconciliation(userID int) {

	conn, err := db.Begin()
	if err != nil {
		fmt.Println("begin failed :", err)
		return
	}

	userRow := conn.QueryRow("select id,balance from users where id=?", userID)
	if userRow == nil {
		panic("user not found")
	}
	var user User
	userRow.Scan(&user.id, &user.balance)
	fmt.Println("conn user.balance:", user.balance)

	fmt.Println("等待2秒钟")
	time.Sleep(2 * time.Second)

	sumMoney := 0
	conn.QueryRow("select sum(number) from user_balance_loggers where user_id=?", user.id).Scan(&sumMoney)

	if sumMoney != user.balance {
		fmt.Printf("对账失败 user.balance:%d sumMoney:%d \n", user.balance, sumMoney)
	} else {
		fmt.Printf("对账成功 user.balance:%d sumMoney:%d \n", user.balance, sumMoney)
	}

	conn.Commit()
}

func reconciliation1(userID int) {

	userRow := db.QueryRow("select id,balance from users where id=? for update", userID) // FOR UPDATE 未在事务中执行，导致行锁失效
	if userRow == nil {
		panic("user not found")
	}
	var user User
	userRow.Scan(&user.id, &user.balance)
	fmt.Println("conn user.balance:", user.balance)

	fmt.Println("等待2秒钟")
	time.Sleep(2 * time.Second)

	sumMoney := 0
	db.QueryRow("select sum(number) from user_balance_loggers where user_id=?", user.id).Scan(&sumMoney)

	if sumMoney != user.balance {
		fmt.Printf("对账失败 user.balance:%d sumMoney:%d \n", user.balance, sumMoney)
	} else {
		fmt.Printf("对账成功 user.balance:%d sumMoney:%d \n", user.balance, sumMoney)
	}

}
